--photos
if OBJECT_ID('photos') is not null drop table photos
go
create table photos(
	id bigint not null,
	name nvarchar(255) not null,
	path nvarchar(255) not null,
	datecreated timestamp not null,
	size int not null,
	userid bigint not null,
	description nvarchar(255) null,
	constraint pk_photos primary key (id),
	constraint fk_photos_users foreign key (userid) references users(id)
)

go

--videos
if OBJECT_ID('videos') is not null drop table videos
go
create table videos(
	id bigint not null,
	name nvarchar(255) not null,
	path nvarchar(255) not null,
	datecreated timestamp not null,
	size int not null,
	userid bigint not null,
	description nvarchar(255) null,
	constraint pk_videos primary key (id),
	constraint fk_videos_users foreign key (userid) references users(id)
)

go

--users
if OBJECT_ID('users') is not null drop table users
go
create table users(
	id bigint not null,
	username nvarchar(255) not null,
	password nvarchar(255) not null,
	emai nvarchar(255) not nulll,
	firstname nvarchar(255) null,
	lastname nvarchar(255) null,
	gender bit null,
	birthday timestamp null,
	confirmed nvarchar(255) not null,
	constraint pk_users primary key (id)
)

go

--assignments
if OBJECT_ID('assignments') is not null drop table assignments
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'fk_assignments_events') and parent_object_id = OBJECT_ID(N'assignments'))
alter table coursemembers drop constraint fk_assignments_events
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'assignments_users') and parent_object_id = OBJECT_ID(N'assignments'))
alter table coursemembers drop constraint assignments_users
go
create table assignments(
	eventid bigint not null,
	userid bigint not null,
	constraint pk_assignments primary key (eventid, userid),
	constraint fk_assignments_events foreign key (userid) references event(id),
	constraint fk_assignments_users foreign key (userid) references users(id)
)

go

--works
if OBJECT_ID('works') is not null drop table works
go
if exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'works') and parent_object_id = OBJECT_ID(N'works'))
alter table courseevent drop constraint fk_works_users
go
create table works(
	id bigint not null,
	userid bigint not null,
	position nvarchar(255) not null,
	description nvarchar(1000),
	startdate date not null,
	universityid bigint not null,
	facultyid bigint not null,
	email varchar(255) not null,
	constraint pk_works primary key (id),
	constraint fk_works_users foreign key (userid ) references users(id),
	constraint fk_works_school foreign key (universityid) references school(id),
	constraint fk_works_faculty foreign key (facultyid) references faculty(id)
)